import javax.servlet.GenericServlet;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class CategorySearchServlet extends HttpServlet {

	String db_path;

	public void init() throws ServletException {
		super.init();
		
		this.db_path = getServletContext().getRealPath("/") + "amazonlite.db";
	}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
		throws ServletException, IOException {
			
			response.setHeader("Content-Type", "text/html");
			
			// load the sqlite-JDBC driver using the current class loader
			try {
				Class.forName("org.sqlite.JDBC");
			}
			catch (Exception e) {
				response.getWriter().write(e.getMessage());
				return;
			}

			Connection connection = null;
			
			try
			{
				// create a database connection
				connection = DriverManager.getConnection("jdbc:sqlite:" + db_path);
				Statement statement = connection.createStatement();
				statement.setQueryTimeout(30);  // set timeout to 30 sec.
				
				String where_part = "WHERE ";
				
				// PREPARE SQL QUERY
				String category = request.getParameter("category");
				String q = request.getParameter("q");
				
				where_part += "category='" + category + "'";
				where_part += " AND (";
				
				where_part += "isbn=" + "'" + q + "'";
				where_part += " OR ";
				where_part += "title LIKE '%" + q + "%" + "'";
				where_part += " OR ";
				where_part += "author LIKE '%" + q + "%" + "'";
				where_part += " OR ";
				where_part += "publisher LIKE '%" + q + "%" + "'";
				where_part += " OR ";
				where_part += "year=" + "'" + q + "'";
				where_part += " OR ";
				where_part += "description LIKE '%" + q + "%" + "'";
				where_part += ")";
				
				String query = "SELECT * FROM books " + where_part;
				
				
				String htmlPage = "<html><head><meta http-equiv=\"Content-type\" content=\"text/html;charset=UTF-8\" /><title>AmazonLite</title><script src=\"validation.js\"></script><link rel=\"stylesheet\" type=\"text/css\" href=\"common.css\" /></head><body>";
				
				ResultSet rs = statement.executeQuery(query);
				ResultSetMetaData rsmd = rs.getMetaData();
				int columns_number = rsmd.getColumnCount();
				
				String table = "<table class=\"display\">";
				table += "<tr><th>ISBN</th><th>Title</th><th>Author</th><th>Publisher</th><th>Year</th><th>Description</th><th>Category</th><th>Price</th><th>Quantity in stock</th></tr>";
			
				while (rs.next()) {
					// read the result set
					table += "<tr>";
					for (int i = 1; i <= columns_number; i++) {
						table += "<td>" + rs.getString(i) + "</td>";
					}
					table += "</tr>";
				}
				
				table += "</table>";
				
				htmlPage += table;
				htmlPage += "</body></html>";
				response.getWriter().write(htmlPage);
			}
			catch(SQLException e)
			{
				// if the error message is "out of memory", 
				// it probably means no database file is found
				System.err.println(e.getMessage());
				
				response.getWriter().write("<html><body>"+e.getMessage()+"</body></html>");
			}
			finally
			{
				try
				{
					if(connection != null) {
						connection.close();
					}
				}
				catch(SQLException e)
				{
					// connection close failed.
					System.err.println(e);
					
					response.getWriter().write("<html><body>PAU2</body></html>");
				}
			}			
						
	}
}
